package bbs;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import util.DB;

public class ArticleDAO {

    private static Article makeArticle(ResultSet rs, boolean includeBody) throws Exception {
        Article obj = new Article();
        obj.setId(rs.getInt("Id"));
        obj.setNo(rs.getInt("no"));
        obj.setUserId(rs.getInt("userId"));
        obj.setName(rs.getString("name"));
        obj.setWriteTime(rs.getTimestamp("writeTime"));
        obj.setTitle(rs.getString("title"));
        obj.setCategory(rs.getString("category"));
        if (includeBody) {
            obj.setBoardId(rs.getInt("boardId"));
            obj.setCategoryId(rs.getInt("categoryId"));
            obj.setBody(rs.getString("body"));
            obj.setNotice(rs.getBoolean("notice"));
            obj.setParentId(rs.getInt("parentId"));
            obj.setDistance(rs.getInt("distance"));
        } else {
            obj.setIndentation(rs.getInt("indentation"));
            obj.setTotalReadCount(rs.getInt("totalReadCount"));
            obj.setMyReadCount(rs.getInt("myReadCount"));
            obj.setCommentCount(rs.getInt("commentCount"));
        }
        return obj;
    }

    public static ArrayList<Article> selectPage(int boardId, int userId, int currentPage, 
            int pageSize, int srchType, String srchText, int categoryId) throws Exception {
        String sql = "EXEC bbs.articleSelectPage ?, ?, ?, ?, ?, ?, ?";
        try (Connection con = DB.getConnection();
             PreparedStatement stmt = con.prepareStatement(sql)) {
            stmt.setInt(1, boardId);
            stmt.setInt(2, userId);
            stmt.setInt(3, currentPage);
            stmt.setInt(4, pageSize);
            stmt.setInt(5, srchType);
            stmt.setString(6, srchText);
            stmt.setInt(7, categoryId);
            try (ResultSet rs = stmt.executeQuery()) {
                ArrayList<Article> list = new ArrayList<Article>();
                while (rs.next())
                    list.add(makeArticle(rs, false));
                return list;
            }
        }
    }

    public static ArrayList<Article> selectNotice(int boardId, int userId) throws Exception {
        String sql = "EXEC bbs.articleSelectNotice ?, ?";
        try (Connection con = DB.getConnection();
             PreparedStatement stmt = con.prepareStatement(sql)) {
            stmt.setInt(1, boardId);
            stmt.setInt(2, userId);
            try (ResultSet rs = stmt.executeQuery()) {
                ArrayList<Article> list = new ArrayList<Article>();
                while (rs.next())
                    list.add(makeArticle(rs, false));
                return list;
            }
        }
    }

    public static int getRecordCount(int boardId, int srchType, String srchText, int category) 
            throws Exception {
        String sql = "EXEC bbs.articleRecordCount ?, ?, ?, ?";
        try (Connection con = DB.getConnection();
             PreparedStatement stmt = con.prepareStatement(sql)) {
            stmt.setInt(1, boardId);
            stmt.setInt(2, srchType);
            stmt.setString(3, srchText);
            stmt.setInt(4, category);
            try (ResultSet rs = stmt.executeQuery()) {
                return rs.next() ? rs.getInt(1) : 0;
            }
        }
    }

    public static Article selectById(int id) throws Exception {
        String sql = "EXEC bbs.articleSelectById ?";
        try (Connection con = DB.getConnection();
             PreparedStatement stmt = con.prepareStatement(sql)) {
            stmt.setInt(1, id);
            try (ResultSet rs = stmt.executeQuery()) {
                return rs.next() ? makeArticle(rs, true) : null;
            }
        }
    }

    public static void insertReadLog(int id, int userId) throws Exception {
        String sql = "EXEC bbs.articleInsertReadLog ?, ?";
        try (Connection con = DB.getConnection();
             PreparedStatement stmt = con.prepareStatement(sql)) {
            stmt.setInt(1, id);
            stmt.setInt(2, userId);
            stmt.executeUpdate();
        }
    }

    public static void insert(int boardId, int categoryId, String title, String body, 
            boolean notice, int userId) throws Exception {
        String sql = "EXEC bbs.articleInsert ?, ?, ?, ?, ?, ?";
        try (Connection con = DB.getConnection();
             PreparedStatement stmt = con.prepareStatement(sql)) {
            stmt.setInt(1, boardId);
            stmt.setInt(2, userId);
            stmt.setInt(3, categoryId);
            stmt.setString(4, title);
            stmt.setString(5, body);
            stmt.setBoolean(6, notice);
            stmt.executeUpdate();
        }
    }

    public static void insertReply(int boardId, int articleId, int categoryId, String title, 
            String body, boolean notice, int userId) throws Exception {
        String sql = "EXEC bbs.articleInsertReply ?, ?, ?, ?, ?, ?, ?";
        try (Connection con = DB.getConnection();
             PreparedStatement stmt = con.prepareStatement(sql)) {
            stmt.setInt(1, boardId);
            stmt.setInt(2, articleId);
            stmt.setInt(3, userId);
            stmt.setInt(4, categoryId);
            stmt.setString(5, title);
            stmt.setString(6, body);
            stmt.setBoolean(7, notice);
            stmt.executeUpdate();
        }
    }

    public static void update(Article article) throws Exception {
        update(article.getId(), article.getCategoryId(), article.getTitle(), 
                article.getBody(), article.isNotice());
    }

    public static void update(int articleId, int categoryId, String title, 
            String body, boolean notice) throws Exception {
        String sql = "UPDATE bbs.[Article] SET [categoryId]=?, [title]=?, [body]=?, [notice]=? " +
                     "WHERE [id]=?";
        try (Connection con = DB.getConnection();
             PreparedStatement stmt = con.prepareStatement(sql)) {
            stmt.setInt(1, categoryId);
            stmt.setString(2, title);
            stmt.setString(3, body);
            stmt.setBoolean(4, notice);
            stmt.setInt(5, articleId);
            stmt.executeUpdate();
        }
    }

    public static void deleteById(int articleId) throws Exception {
        String sql = "EXEC bbs.articleDeleteById ?";
        try (Connection con = DB.getConnection();
             PreparedStatement stmt = con.prepareStatement(sql)) {
            stmt.setInt(1, articleId);
            stmt.executeUpdate();
        }
    }
}